﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KBVault.DAL.Entities;
using Codebricks.Db;
using Codebricks.Db.Extensions;
using System.Data;

namespace KBVault.DAL.Repository
{
    public class ArticleRepository:BaseRepository
    {
        private static string GetLatestArticlesCommand = "select * from articles where Is_Draft = 0 order by Edited desc limit 0,{0}";
        private static string GetPopularArticlesCommand = "select * from articles where Is_Draft = 0 order by Views desc limit 0,{0}";
        private static string GetSimilarArticlesCommand = "Select  t.*, sum(Relevance) as Relevance from( " +
                                                            "select article_id,tag_id, ( " +
                                                            " Select Count(*) from article_tags i " +
                                                            " where i.article_id = @ArticleId and tag_id = o.tag_id ) Relevance " +
                                                            "from article_tags o where article_id <>  @ArticleId) x left join articles t on x.article_id = t.id " +
                                                            "group by article_id " +
                                                            "having sum(Relevance) > 0 order by sum(Relevance) desc  limit 0,5";

        private static string GetArticleTagsCommand = "select * from article_tags where article_id = @Id";
        private static string DeleteArticleTagsCommand = "delete from article_tags where article_id=@Id";
        private static string InsertTagToArticleCommand = "insert into article_tags(article_id,tag_id) values(@ArticleId,@TagId)";

        public static void Create(ref Article a)
        {
            try
            {
                a.Id = (int) MySqlDb.Insert(a);
                DeleteArticleTags(a.Id);
                InsertArticleTags(a);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                throw;
            }
        }

        public static void Update(Article a)
        {
            try
            {
                MySqlDb.Update(a);
                DeleteArticleTags(a.Id);
                InsertArticleTags(a);
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }
        
        public static void InsertArticleTags(Article a)
        {
            try
            {
                foreach (Tag t in a.Tags)
                {
                    MysqlParameterCollection col = new MysqlParameterCollection();
                    col.Add("ArticleId", a.Id);
                    col.Add("TagId", t.Id);
                    MySqlDb.ExecuteQuery(InsertTagToArticleCommand, col);
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }
        public static void DeleteArticleTags(int articleId)
        {
            try
            {
                MysqlParameterCollection col = new MysqlParameterCollection();
                col.Add("Id", articleId);
                MySqlDb.ExecuteQuery(DeleteArticleTagsCommand, col);
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }
        public static void Delete(int id)
        {
            try
            {
                
                Article a = new Article() { Id = id };
                DeleteArticleTags(id);
                MySqlDb.Delete(a);
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static Article Get(int id)
        {
            try
            {
                Article a = new Article() { Id = id };
                a = MySqlDb.Get<Article>(a);
                a.Tags = GetArticleTags(a.Id);
                return a;
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static List<Article> GetLatestArticles(int count=10)
        {
            try
            {
                List<Article> articles = new List<Article>();
                DataTable dt = MySqlDb.Select(String.Format(GetLatestArticlesCommand,count), null);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        Article a = new Article();
                        a.CreateObjectFromDataRow(row);
                        articles.Add(a);
                    }
                }
                return articles.Where(a => DateTime.Now.Date >= a.PublishStartDate && DateTime.Now.Date <= a.PublishEndDate).ToList<Article>();
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static List<Article> GetSimilarArticles( int articleId )
        {
         try
            {
                List<Article> articles = new List<Article>();
                MysqlParameterCollection col = new MysqlParameterCollection();
                col.Add("ArticleId", articleId);
                DataTable dt = MySqlDb.Select(GetSimilarArticlesCommand, col);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        Article a = new Article();
                        a.CreateObjectFromDataRow(row);
                        articles.Add(a);
                    }
                }
                return articles;
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static List<Tag> GetArticleTags( int articleId )
        {
            try
            {
                List<Tag> tags = new List<Tag>();
                MysqlParameterCollection col = new MysqlParameterCollection();
                col.Add("Id",articleId);
                DataTable dt = MySqlDb.Select(GetArticleTagsCommand, col);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        tags.Add(TagRepository.Get(Convert.ToInt32(row["tag_id"])));
                    }
                }
                return tags;

            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }

        public static List<Article> GetPopularArticles(int count=10)
        {
            try
            {
                List<Article> articles = new List<Article>();
                DataTable dt = MySqlDb.Select(String.Format(GetPopularArticlesCommand,count), null);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        Article a = new Article();
                        a.CreateObjectFromDataRow(row);
                        articles.Add(a);
                    }
                }
                return articles.Where(a => DateTime.Now.Date >= a.PublishStartDate && DateTime.Now.Date <= a.PublishEndDate).ToList<Article>();
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
                throw;
            }
        }
        
    }
}
